Drop Procedure dbo.sp_GenerateClass
go
--Exec sp_GenerateClass 'NameSpace'
Create Procedure dbo.sp_GenerateClass
(
	@namespace				sysname, 
	@Assembly				sysname		= null, 
	@tabela					sysname		= null,
	@SequenceTableName		sysname		= 'sequence',
	@SequenceDescription	sysname		= 'name',
	@SequenceId				sysname		= 'id',
	@column_has_prefix		bit			= 1
)
As
Begin
	Set NoCount On
	Declare @classe			table (tabela varchar(1024), linha varchar(2048))
	Declare @coluna			sysname
	Declare @propriedade	sysname
	Declare @tipo			sysname
	Declare @tipoBD			sysname
	Declare @tamanho		smallint
	Declare @decimal		smallint
	Declare @is_nullable	bit
	Declare @atrib			sysname
	Declare @nomeColuna		sysname
	Declare @tabela_filha	sysname
	Declare @coluna_filha	sysname
	Declare	@schema			sysname

	Insert Into @classe Values(Null, 'using System;')
	Insert Into @classe Values(Null, 'using Normal;')
	Insert Into @classe Values(Null, 'using System.Collections.Generic;')
	Insert Into @classe Values(Null, '')
	Insert Into @classe Values(Null, 'namespace ' + @namespace)
	Insert Into @classe Values(Null, '{')

	Declare C_Tabelas Insensitive Cursor For 
		Select t.name, s.name
		From sys.tables t
		Join sys.schemas s 
		On s.schema_id = t.schema_id 
		Where (t.name = @tabela or @tabela Is Null) 
		Order By 2,1
	
	Open C_Tabelas
	Fetch C_Tabelas Into @tabela, @schema

	While (@@Fetch_status = 0)
	Begin
		if @tabela like 'sys%'
		Begin
			Fetch C_Tabelas Into @tabela, @schema
			Continue
		End
		Insert Into @classe Values(@tabela, Char(9) + '[Serializable]')
		Insert Into @classe Values(@tabela, Char(9) + '[Table(TableName="' + @schema + '.' + @tabela + '")]')
		Insert Into @classe Values(@tabela, Char(9) + 'public class ' + dbo.fn_NormalizateClassName(@tabela) + ' : ContextBoundObject')
		Insert Into @classe Values(@tabela, Char(9) + '{')
		Declare C_Colunas Insensitive Cursor For 
			Select 
				Case
						When @column_has_prefix	= 1 Then dbo.fn_NormalizateFieldName(name, 2)
						Else dbo.fn_NormalizateFieldName(name, 1)
				End,
				dbo.fn_GetDotNetType(type_name(system_type_id), is_nullable),
				type_name(system_type_id),
				max_length,
				scale,
				is_nullable,
				name
			From sys.columns
			Where object_id = object_id(@schema + '.' + @tabela)
			Order By column_id
		Open C_Colunas
		Fetch C_Colunas Into @coluna, @tipo, @tipoBD, @tamanho, @decimal, @is_nullable, @nomeColuna
		While (@@Fetch_Status = 0)
		Begin
			Set @propriedade = '_' + Lower(Left(@coluna, 1)) + Substring(@coluna, 2, Len(@coluna))
			If Exists(
						Select 1
						From sys.foreign_keys f
						Join sys.foreign_key_columns fc
						On	f.object_id = fc.constraint_object_id
						Join sys.tables t
						On	t.object_id = fc.referenced_object_id
						Join sys.columns c
						On	c.object_id	= t.object_id
						And	c.column_id	= fc.referenced_column_id
						Join sys.columns c1
						On  c1.object_id	= fc.parent_object_id
						And	c1.column_id	= fc.parent_column_id
						Where	f.parent_object_id	= object_id(@schema + '.' + @tabela)
						And		c1.name				= @nomeColuna
				)
			Begin
				Select
					@tipo		=	dbo.fn_NormalizateClassName(t.name)
				From sys.foreign_keys f
				Join sys.foreign_key_columns fc
				On	f.object_id = fc.constraint_object_id
				Join sys.tables t
				On	t.object_id = fc.referenced_object_id
				Join sys.columns c
				On	c.object_id	= t.object_id
				And	c.column_id	= fc.referenced_column_id
				Join sys.columns c1
				On  c1.object_id	= fc.parent_object_id
				And	c1.column_id	= fc.parent_column_id
				Where	f.parent_object_id	= object_id(@schema + '.' + @tabela)
				And		c1.name				= @nomeColuna
			End
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + 'private ' + @tipo + ' ' + @propriedade + ';')
			Fetch C_Colunas Into @coluna, @tipo, @tipoBD, @tamanho, @decimal, @is_nullable, @nomeColuna
		End
		Deallocate C_Colunas
		
		Declare C_FK1 Insensitive Cursor For
			Select Distinct
				tabela_filha	=	dbo.fn_NormalizateClassName(t.name)
			From sys.foreign_keys f
			Join sys.foreign_key_columns fc
			On	f.referenced_object_id = fc.referenced_object_id
			Join sys.tables t
			On	t.object_id = fc.parent_object_id
			Join sys.columns c
			On	c.object_id	= t.object_id
			And	c.column_id	= fc.parent_column_id
			Where f.referenced_object_id	= object_id(@schema + '.' + @tabela)
			And c.is_nullable = 0
			Order By 1
		Open C_FK1
		Fetch C_FK1 Into @tabela_filha
		While (@@Fetch_status = 0)
		Begin
			Set @propriedade = '_' + Lower(Left(@tabela_filha, 1)) + Substring(@tabela_filha, 2, Len(@tabela_filha))
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + 'private List<' + @tabela_filha + '> ' + @propriedade + 's;')
			Fetch C_FK1 Into @tabela_filha
		End
		Deallocate C_FK1

		Insert Into @classe Values(@tabela, '')

		Declare C_Colunas2 Insensitive Cursor For 
			Select 
				Case
						When @column_has_prefix	= 1 Then dbo.fn_NormalizateFieldName(name, 2)
						Else dbo.fn_NormalizateFieldName(name, 1)
				End,
				dbo.fn_GetDotNetType(type_name(system_type_id), is_nullable),
				type_name(system_type_id),
				max_length,
				scale,
				is_nullable,
				name
			From sys.columns
			Where object_id = object_id(@schema + '.' + @tabela)
			Order By column_id
		Open C_Colunas2
		Fetch C_Colunas2 Into @coluna, @tipo, @tipoBD, @tamanho, @decimal, @is_nullable, @nomeColuna
		While (@@Fetch_Status = 0)
		Begin
			Set @propriedade = '_' + Lower(Left(@coluna, 1)) + Substring(@coluna, 2, Len(@coluna))
			Set @atrib = '[Column(Name="' + @nomeColuna + '"'
			If @is_nullable = 0 
				Set @atrib = @atrib + ', Required=true'
			Else
				Set @atrib = @atrib + ', Required=false'
			Set @atrib = @atrib + ')]'
			If Exists(
						Select 1
						From sys.indexes i
						Join sys.index_columns ic
						On		ic.object_id						= i.object_id
						And		ic.index_id							= i.index_id
						Join sys.columns c
						On		ic.object_id						= c.object_id
						And		ic.column_id						= c.column_id
						Where	i.object_id							= object_id(@schema + '.' + @tabela)
						And		i.is_primary_key					= 1
						And		Case
									When @column_has_prefix	= 1 Then dbo.fn_NormalizateFieldName(c.name, 2)
									Else dbo.fn_NormalizateFieldName(c.name, 1)
								End	= @coluna
				)
			Begin
				Set @atrib = '[Id(Name="' + @nomeColuna + '")]'
				Insert Into @classe Values(@tabela, Char(9) + Char(9) + @atrib)
				If Exists(
							Select 1 
							From sys.identity_columns iic
							Join sys.columns c
							On iic.object_id	= c.object_id
							And	iic.column_id	= c.column_id
							Where	iic.object_id	= object_id(@schema + '.' + @tabela)
							And		c.name			= @nomeColuna
							)
				Begin
					Insert Into @classe Values(@tabela, Char(9) + Char(9) + '[AutoNumber(Strategy=AutoNumberStrategy.Native)]')
				End
				Else
				Begin
					Set @atrib = '[AutoNumber(Strategy=AutoNumberStrategy.Sequence'
					Insert Into @classe Values(@tabela, Char(9) + Char(9) + @atrib)
					Set @atrib = ', SequenceTableName="' + @SequenceTableName + '"'
					Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + @atrib)
					Set @atrib = ', SequenceDescription="' + @SequenceDescription + '"'
					Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + @atrib)
					Set @atrib = ', SequenceId="' + @SequenceId + '")]'
					Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + @atrib)
				End
			End
			Else
				Insert Into @classe Values(@tabela, Char(9) + Char(9) + @atrib)
			If Exists(
						Select 1
						From sys.foreign_keys f
						Join sys.foreign_key_columns fc
						On	f.object_id = fc.constraint_object_id
						Join sys.tables t
						On	t.object_id = fc.referenced_object_id
						Join sys.columns c
						On	c.object_id	= t.object_id
						And	c.column_id	= fc.referenced_column_id
						Join sys.columns c1
						On  c1.object_id	= fc.parent_object_id
						And	c1.column_id	= fc.parent_column_id
						Where	f.parent_object_id	= object_id(@schema + '.' + @tabela)
						And		c1.name				= @nomeColuna
				)
			Begin
				Select
					@tipo		=	dbo.fn_NormalizateClassName(t.name)
				From sys.foreign_keys f
				Join sys.foreign_key_columns fc
				On	f.object_id = fc.constraint_object_id
				Join sys.tables t
				On	t.object_id = fc.referenced_object_id
				Join sys.columns c
				On	c.object_id	= t.object_id
				And	c.column_id	= fc.referenced_column_id
				Join sys.columns c1
				On  c1.object_id	= fc.parent_object_id
				And	c1.column_id	= fc.parent_column_id
				Where	f.parent_object_id	= object_id(@schema + '.' + @tabela)
				And		c1.name				= @nomeColuna
			End
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + 'public ' + @tipo + ' ' + @coluna)
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '{')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + ' get { return ' + @propriedade + '; }')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + ' set { ' + @propriedade + ' = value; }')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '}')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '')
			Fetch C_Colunas2 Into @coluna, @tipo, @tipoBD, @tamanho, @decimal, @is_nullable, @nomeColuna
		End
		Deallocate C_Colunas2
		Declare C_FK2 Insensitive Cursor For
			Select Distinct
				tabela_filha	=	dbo.fn_NormalizateClassName(t.name)
			From sys.foreign_keys f
			Join sys.foreign_key_columns fc
			On	f.referenced_object_id = fc.referenced_object_id
			Join sys.tables t
			On	t.object_id = fc.parent_object_id
			Join sys.columns c
			On	c.object_id	= t.object_id
			And	c.column_id	= fc.parent_column_id
			Where f.referenced_object_id	= object_id(@schema + '.' + @tabela)
			And c.is_nullable = 0
			Order By 1
		Open C_FK2
		Fetch C_FK2 Into @tabela_filha
		While (@@Fetch_status = 0)
		Begin
			Set @propriedade = '_' + Lower(Left(@tabela_filha, 1)) + Substring(@tabela_filha, 2, Len(@tabela_filha)) + 's'
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + 'public List<' + @tabela_filha + '> ' + @tabela_filha + 's')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '{')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + ' get { return ' + @propriedade + '; }')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + Char(9) + ' set { ' + @propriedade + ' = value; }')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '}')
			Insert Into @classe Values(@tabela, Char(9) + Char(9) + '')
			Fetch C_FK2 Into @tabela_filha
		End
		Deallocate C_FK2

		Insert Into @classe Values(@tabela, Char(9) + '}')
		Fetch C_Tabelas Into @tabela, @schema
	End
	Deallocate C_Tabelas
	Insert Into @classe Values(Null, '}')
	Insert Into @classe Values(Null, '')

	Select linha From @classe
End
